import pandas as pd
import chardet
import csv
import logging
from pathlib import Path
import sys
from datetime import datetime

def excel_to_csv(input_path, output_path=None, sheet_name=0, delimiter=',', encoding='auto', 
                 skip_lines=0, keep_formula=False, preserve_numeric=True, chunksize=10000):
    """
    将 Excel 文件高效转换为 CSV 格式
    
    参数:
    input_path: Excel 文件路径 (支持 .xlsx, .xls)
    output_path: 输出 CSV 文件路径 (默认为输入文件同目录)
    sheet_name: 工作表名称或索引 (默认为第一个)
    delimiter: CSV 分隔符 (默认 ",")
    encoding: 输出编码 (默认自动检测)
    skip_lines: 跳过起始行数 (默认 0)
    keep_formula: 保留公式而非计算结果 (默认 False)
    preserve_numeric: 保留数值精度 (默认 True)
    chunksize: 大文件分块处理行数 (默认 10000)
    """
    start_time = datetime.now()
    
    # 设置输出路径
    in_path = Path(input_path)
    if not output_path:
        output_path = in_path.with_suffix('.csv')
    out_path = Path(output_path)
    
    # 验证文件格式
    if in_path.suffix.lower() not in ['.xlsx', '.xls']:
        logging.error(f"错误: 不支持的扩展名 {in_path.suffix}")
        return False
    
    # 配置日志
    logging.basicConfig(
        level=logging.INFO,
        format='%(asctime)s - %(levelname)s - %(message)s',
        handlers=[
            logging.FileHandler("excel_to_csv.log", encoding='utf-8'),
            logging.StreamHandler()
        ]
    )
    
    logging.info(f"开始转换: {in_path.name} -> {out_path.name}")
    
    try:
        # 确定最佳解析引擎
        engine = 'openpyxl' if in_path.suffix.lower() == '.xlsx' else 'xlrd'
        
        # 大文件分块处理
        total_rows = 0
        with out_path.open('w', newline='', encoding='utf-8') if encoding == 'auto' else \
             out_path.open('w', newline='', encoding=encoding) as csv_file:
            
            writer = None
            
            for chunk_idx, chunk in enumerate(pd.read_excel(
                in_path, 
                sheet_name=sheet_name, 
                skiprows=skip_lines,
                dtype='str' if not preserve_numeric else None,  # 保留数值格式
                converters={} if keep_formula else None,        # 保留公式
                engine=engine,
                na_values=['', '#N/A', '#NULL!'],              # 自定义空值处理
                chunksize=chunksize
            )):
                # 检测并处理列名中的特殊字符
                chunk.columns = [
                    col.replace('\n', ' ').replace('\r', '') 
                    for col in chunk.columns
                ]
                
                # 写入标题行
                if chunk_idx == 0:
                    # 自动检测编码
                    if encoding == 'auto':
                        detected = chardet.detect(chunk.to_csv(index=False).encode())
                        best_encoding = detected['encoding'] or 'utf-8'
                        if best_encoding.lower() in ['gb2312', 'gb18030']:
                            best_encoding = 'gb18030'
                        csv_file.close()
                        csv_file = out_path.open('w', newline='', encoding=best_encoding)
                    
                    writer = csv.writer(csv_file, delimiter=delimiter)
                    writer.writerow(chunk.columns)
                    encoding_used = best_encoding if encoding == 'auto' else encoding
                
                # 格式处理
                if not preserve_numeric:
                    chunk = chunk.applymap(
                        lambda x: round(x, 10) if isinstance(x, float) else x
                    )
                
                # 写入数据行
                for row in chunk.itertuples(index=False, name=None):
                    writer.writerow(row)
                
                total_rows += len(chunk)
                logging.info(f"已处理 {total_rows} 行")
        
        # 添加文件元信息
        out_path.write_text(
            out_path.read_text(encoding=encoding_used) + 
            f"\n# Generated from: {in_path.name} at {datetime.now().strftime('%Y-%m-%d %H:%M')}",
            encoding=encoding_used
        )
        
    except Exception as e:
        logging.exception("转换失败:")
        return False
    finally:
        # 清理临时文件
        if 'workbook' in locals():
            workbook.close()
    
    elapsed = (datetime.now() - start_time).total_seconds()
    logging.info(f"转换完成! 行数: {total_rows}, 耗时: {elapsed:.2f}秒")
    logging.info(f"输出编码: {encoding_used if encoding == 'auto' else encoding}")
    
    return True

if __name__ == "__main__":
    # 命令行支持
    if len(sys.argv) > 1:
        input_file = sys.argv[1]
        output_file = sys.argv[2] if len(sys.argv) > 2 else None
        excel_to_csv(input_file, output_file)
    else:
        # 示例用法
        excel_to_csv(
            input_path="/data/financial_report.xlsx",
            output_path="/reports/financial_q3.csv",
            sheet_name="Quarterly Results",
            delimiter=";",  # 欧盟常用分隔符
            encoding="utf-8-sig",  # 带BOM的UTF-8
            skip_lines=2,    # 跳过标题说明
            preserve_numeric=True
        )